<?php 
class home_model extends CI_model{


	function get_banyak_perusahaan(){
		$q = $this->db->query("SELECT COUNT(id_perusahaan) AS banyak
							   FROM perusahaan");
		return $q->result();
	}

	function get_sudah(){
		$q = $this->db->query("SELECT COUNT(id_perusahaan) AS banyak
							   FROM perusahaan
							   WHERE status_marketing_perusahaan <> 'belum'");
		return $q->result();
	}

	function get_belum(){
		$q = $this->db->query("SELECT COUNT(id_perusahaan) AS banyak
							   FROM perusahaan
							   WHERE status_marketing_perusahaan = 'belum'");
		return $q->result();
	}

	function get_belum_tugas_marketing(){
		$q = $this->db->query("SELECT pm.id_perusahaan,pm.id_marketer,p.id_perusahaan, COUNT(p.id_perusahaan) AS banyak
							   FROM penugasan_marketing pm
							   JOIN 
							   perusahaan p ON pm.id_perusahaan=p.id_perusahaan
							   WHERE pm.id_marketer IS NULL");
		return $q->result();
	}


	function get_belum_tugas_followup(){
		$q = $this->db->query("SELECT *, pm.id_perusahaan as pfid_perusahaan, pm.id_marketer as pfid_marketer, COUNT(pm.id_penugasan_marketing) AS banyak
							   FROM penugasan_marketing pm
							   JOIN 
							   perusahaan p ON pm.id_perusahaan=p.id_perusahaan
							   JOIN 
							   marketer m ON pm.id_marketer=m.id_marketer
							   WHERE p.status_marketing_perusahaan='positif' AND pm.penugasan_followup !=1 
							   ORDER BY pm.id_penugasan_marketing DESC");
		return $q->result();
	}


	function detail_penugasan_followup($id){
		$q = $this->db->query("SELECT *
							   FROM penugasan_followup pf
							   JOIN 
							   (SELECT p.id_perusahaan,p.nama_perusahaan, p.status_marketing_perusahaan, p.kategori_perusahaan, p.alamat_perusahaan, p.longitude_perusahaan, p.latitude_perusahaan, p.keterangan_perusahaan, pm.id_penugasan_marketing, pm.deskripsi_hasil_marketing, pm.id_marketer, m.nama_marketer
							   FROM penugasan_marketing pm
							   JOIN 
							   perusahaan p ON pm.id_perusahaan=p.id_perusahaan
							   JOIN 
							   marketer m ON pm.id_marketer=m.id_marketer
							   ) m ON pf.id_penugasan_marketing=m.id_penugasan_marketing
							   JOIN
							   teknisi t ON pf.id_teknisi=t.id_teknisi
							   WHERE id_penugasan_followup=$id");
		return $q->result();
	}
}
?>